{
 "cells": [
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "\n",
    "\n",
    "#### **第一部分：数据预处理**\n",
    "\n",
    "```python\n",
    "# 1. 导入 Pandas 和 NumPy 库，用于数据处理\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 2. 加载数据集\n",
    "# 使用 Pandas 的 read_csv 函数加载数据集 train_users_2.csv\n",
    "df = pd.read_csv('train_users_2.csv')  # 假设文件名为 train_users_2.csv\n",
    "print(df.info())  # 查看数据集的基本信息，如列名、数据类型和缺失值情况\n",
    "print(df.head())  # 查看数据集的前几行内容，帮助了解数据结构\n",
    "\n",
    "# 3. 替换 \"-unknown-\" 为 NaN（缺失值）\n",
    "# 数据集中 \"-unknown-\" 表示未知值，将其替换为 NaN，方便后续处理\n",
    "df.replace(\"-unknown-\", np.nan, inplace=True)\n",
    "\n",
    "# 4. 检查缺失值\n",
    "# 使用 isnull().sum() 检查每列的缺失值数量，输出帮助理解数据质量\n",
    "print(df.isnull().sum())\n",
    "\n",
    "# 5. 处理 age 列中的异常值\n",
    "# 如果 age 不在 0-100 的合理范围内，将其替换为 NaN\n",
    "df['age'] = df['age'].apply(lambda x: x if (x > 0 and x <= 100) else np.nan)\n",
    "\n",
    "# 6. 填充 age 列中的缺失值\n",
    "# 使用中位数填充 age 列中的 NaN 值，以减少数据偏差\n",
    "df['age'].fillna(df['age'].median(), inplace=True)\n",
    "\n",
    "# 7. 处理时间戳字段\n",
    "# 将 timestamp_first_active 转换为标准日期时间格式\n",
    "df['timestamp_first_active'] = pd.to_datetime(df['timestamp_first_active'], format='%Y%m%d%H%M%S')\n",
    "\n",
    "# 8. 计算日期差\n",
    "# 计算 date_account_created 和 timestamp_first_active 之间的时间差（以天为单位），并新增一列 days_difference\n",
    "df['date_account_created'] = pd.to_datetime(df['date_account_created'])\n",
    "df['days_difference'] = (df['date_account_created'] - df['timestamp_first_active']).dt.days\n",
    "\n",
    "# 9. 删除冗余列\n",
    "# 删除缺失率过高或不相关的列（例如 date_first_booking）\n",
    "if 'date_first_booking' in df.columns:\n",
    "    df.drop(columns=['date_first_booking'], inplace=True)\n",
    "\n",
    "# 10. 统一语言列的格式\n",
    "# 将 language 列中的值全部转换为小写，确保数据一致性\n",
    "df['language'] = df['language'].str.lower()\n",
    "```\n",
    "\n",
    "------\n",
    "\n",
    "#### **第二部分：数据转换**\n",
    "\n",
    "```python\n",
    "# 11. 创建年龄分组列\n",
    "# 根据 age 列的值创建年龄分组列 age_group\n",
    "def age_grouping(age):\n",
    "    if age <= 18:\n",
    "        return '0-18岁'\n",
    "    elif 19 <= age <= 30:\n",
    "        return '19-30岁'\n",
    "    elif 31 <= age <= 45:\n",
    "        return '31-45岁'\n",
    "    else:\n",
    "        return '46岁及以上'\n",
    "\n",
    "# 应用分组规则，将结果存储在新的列 age_group 中\n",
    "df['age_group'] = df['age'].apply(age_grouping)\n",
    "\n",
    "# 12. 提取活跃年\n",
    "# 从 timestamp_first_active 中提取年份，并存储在 active_year 列中\n",
    "df['active_year'] = df['timestamp_first_active'].dt.year\n",
    "\n",
    "# 13. 分类变量值的频率统计\n",
    "# 分别统计 gender 和 signup_method 列中每个值出现的次数\n",
    "print(df['gender'].value_counts())  # 输出性别分布\n",
    "print(df['signup_method'].value_counts())  # 输出注册方式分布\n",
    "```\n",
    "\n",
    "------\n",
    "\n",
    "#### **第三部分：数据透视表分析**\n",
    "\n",
    "```python\n",
    "# 14. 用户平均年龄分析\n",
    "# 创建一个透视表，按 gender（性别）和 country_destination（目标国家）分组，计算用户的平均年龄\n",
    "pivot_avg_age = df.pivot_table(\n",
    "    values='age',  # 要计算的字段为 age\n",
    "    index='gender',  # 行索引为 gender\n",
    "    columns='country_destination',  # 列索引为 country_destination\n",
    "    aggfunc='mean'  # 计算平均值\n",
    ")\n",
    "print(pivot_avg_age)  # 输出透视表结果\n",
    "\n",
    "# 15. 注册方式与设备类型分布\n",
    "# 创建一个透视表，按 signup_method（注册方式）和 first_device_type（首次设备类型）分组，统计用户数量\n",
    "pivot_signup_device = df.pivot_table(\n",
    "    values='id',  # 假设 id 是唯一标识符，计算每组中的数量\n",
    "    index='signup_method',  # 行索引为 signup_method\n",
    "    columns='first_device_type',  # 列索引为 first_device_type\n",
    "    aggfunc='count'  # 计算数量\n",
    ")\n",
    "print(pivot_signup_device)  # 输出透视表结果\n",
    "\n",
    "# 16. 推广渠道与目标国家关系\n",
    "# 创建一个透视表，按 affiliate_channel（推广渠道）和 country_destination（目标国家）分组，统计用户数量\n",
    "pivot_affiliate_country = df.pivot_table(\n",
    "    values='id',\n",
    "    index='affiliate_channel',\n",
    "    columns='country_destination',\n",
    "    aggfunc='count'\n",
    ")\n",
    "print(pivot_affiliate_country)\n",
    "\n",
    "# 17. 设备类型和浏览器组合分析\n",
    "# 创建一个透视表，按 first_device_type（设备类型）和 first_browser（浏览器）分组，统计用户数量\n",
    "pivot_device_browser = df.pivot_table(\n",
    "    values='id',\n",
    "    index='first_device_type',\n",
    "    columns='first_browser',\n",
    "    aggfunc='count'\n",
    ")\n",
    "print(pivot_device_browser)\n",
    "\n",
    "# 18. 注册时间与活跃时间关系\n",
    "# 创建一个透视表，按 active_year（活跃年）和 date_account_created（账户创建日期）分组，统计用户数量\n",
    "pivot_active_date = df.pivot_table(\n",
    "    values='id',\n",
    "    index='active_year',\n",
    "    columns='date_account_created',\n",
    "    aggfunc='count'\n",
    ")\n",
    "print(pivot_active_date)\n",
    "```\n",
    "\n",
    "------\n",
    "\n"
   ],
   "id": "8a0f5f9740ace527"
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "",
   "id": "bdf0dc84a2480a8"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
